﻿using HandyControl.Controls;
using Microsoft.VisualBasic.ApplicationServices;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Data;
using System.IO;
using System.Reflection;

namespace BloodApp.Common
{
    public static class EPPlusExcelService
    {
        #region ===导入
        public static string ImportExcelFilePath()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel Files (*.xlsx; *.xls)|*.xlsx; *.xls";
            openFileDialog.FilterIndex = 1;
            openFileDialog.Multiselect = false;

            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                var selectedFile = openFileDialog.FileName;
                if (string.IsNullOrEmpty(selectedFile))
                {
                    return null;
                }
                return selectedFile;
            }
            else
                return null;
        }

        public static (bool, List<List<string>>) ColumnValueList(string path)
        {
            if (string.IsNullOrEmpty(path))
                return (false, null);
            var list = new List<List<string>>();
            try
            {
                ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

                using (ExcelPackage package = new(path))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                    //获取表格的列数和行数
                    int rowCount = worksheet.Dimension.Rows;
                    int colCount = worksheet.Dimension.Columns;
                    var dic = new Dictionary<Dictionary<int, int>, string>();
                    // 循环遍历工作表中的数据

                    for (int col = 1; col <= colCount; col++)
                    {
                        var li = new List<string>();
                        for (int row = 1; row <= rowCount; row++)
                        {
                            li.Add(worksheet.Cells[row, col].Value?.ToString());
                        }
                        list.Add(li);
                    }

                }
            }
            catch (Exception ex)
            {
                return (false, null);
            }

            return (true, list);
        }


        #endregion

        #region ===导出
        public static bool ExportExcel(List<List<string>> list, string fileName = "", string sheet1 = "", string title = "")
        {
            if (list == null || list.Count == 0)
                return false;
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//指明非商用 没有的话无法保存
            try
            {
                FolderBrowserDialog folderBrowserDialog = new();
                folderBrowserDialog.Description = "请选择文件夹";
                int startRow = 0;
                if (folderBrowserDialog.ShowDialog() != DialogResult.OK)
                    return false;
                if (folderBrowserDialog.SelectedPath == string.Empty)
                    return false;
                // 设置Excel文件路径
                if (string.IsNullOrEmpty(fileName))
                    fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
                string filePath = folderBrowserDialog.SelectedPath + @"\" + fileName + ".xlsx";
                // 创建一个新的Excel包
                FileInfo file = new FileInfo(filePath);
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    var worksheet = package.Workbook.Worksheets.Add("Sheet1");
                    if (!string.IsNullOrEmpty(title))
                    {
                        startRow = 1;
                        string strRange = "A1:M1";
                        worksheet.Cells[strRange].Merge = true;
                        worksheet.Cells[strRange].Value = title;
                        worksheet.Row(startRow).Height = 25;
                        worksheet.Cells[strRange].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[strRange].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[strRange].Style.Font.Bold = true;
                        worksheet.Cells[strRange].Style.Font.Size = 18;
                    }
                    //Add the headers
                    for (int i = 0; i < list.Count; i++)
                    {
                        for (int j = 0; j < list[i].Count; j++)
                        {
                            worksheet.Cells[j + 1 + startRow, i + 1].Value = list[i][j];
                        }
                    }
                    worksheet.Cells.AutoFitColumns(35);  //Autofit columns for all cells
                    // 保存Excel文件
                    package.Save();
                    // 释放资源
                    package.Dispose();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static bool DataToExcel(DataTable dataTable, string fileName = "")
        {
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 使用非商业许可
                // 1.创建一个Excel文件
                FolderBrowserDialog folderBrowserDialog = new();
                folderBrowserDialog.Description = "请选择文件夹";
                if (folderBrowserDialog.ShowDialog() != DialogResult.OK)
                    return false;
                if (folderBrowserDialog.SelectedPath == string.Empty)
                    return false;
                // 设置Excel文件路径
                if (string.IsNullOrEmpty(fileName))
                    fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
                string filePath = folderBrowserDialog.SelectedPath + @"\" + fileName + ".xlsx";
                using (var excelPackage = new ExcelPackage())
                {
                    // 2.创建一个ExcelSheet 工作表  
                    ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
                    // 冻结标题（行号，列号）
                    worksheet.View.FreezePanes(29, 1);
                    //设置第一行列标题样式
                    using (ExcelRange r = worksheet.Cells[1, 1, 1, dataTable.Columns.Count])
                    {
                        r.Style.Font.Color.SetColor(System.Drawing.Color.White);//字体颜色
                        r.Style.Font.Bold = true;
                        r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));//背景颜色
                    }
                    //worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
                    //worksheet.Row(1).CustomHeight = true;//自动调整行高
                    //worksheet.Column(1).Width = 15;//设置列宽
                    //worksheet.Row(1).Height = 15;//设置行高

                    ////单元格合并
                    //worksheet.Cells[$"A{row}:F{row}"].Merge = true;
                    //worksheet.Cells[$"A{row}"].Style.WrapText = true;
                    //worksheet.SelectedRange[$"A{row}"].Value = purchaseHistory[0].LineText;
                    //worksheet.Cells[$"A{row}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    //worksheet.Cells[$"A{row}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    // 3.将数据写入到Excel中
                    worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);
                    worksheet.Cells.AutoFitColumns(50, 100);// 自动调整列宽 可能会对性能产生一定影响
                    // 4.保存文件 保存Excel文件  
                    if (File.Exists(filePath))
                        File.Delete(filePath);
                    excelPackage.SaveAs(new FileInfo(filePath));
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static bool ExportBigExcelTest()
        {
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//指明非商用 没有的话无法保存
                FolderBrowserDialog folderBrowserDialog = new();
                folderBrowserDialog.Description = "请选择文件夹";
                if (folderBrowserDialog.ShowDialog() != DialogResult.OK)
                    return false;
                if (folderBrowserDialog.SelectedPath == string.Empty)
                    return false;
                // 设置Excel文件路径
                string filePath = folderBrowserDialog.SelectedPath + @"\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                using (var package = new ExcelPackage())
                {
                    var worksheet = package.Workbook.Worksheets.Add("Sheet1");
                    worksheet.Cells.AutoFitColumns(50, 100);// 自动调整列宽 可能会对性能产生一定影响
                    // 假设我们要写入10000行数据
                    for (int row = 1; row <= 100000; row++)
                    {
                        for (int col = 1; col <= 5; col++)
                        {
                            worksheet.Cells[row, col].Value = $"Row {row}, Col {col}";
                        }
                        // 每写入一定数量的行后调用 Save 方法，以控制内存使用
                        if (row % 10000 == 0)
                           package.SaveAs(new FileInfo(filePath));
                    }
                    // 最终保存
                    package.SaveAs(new FileInfo(filePath));
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;   
            }
        }

        #endregion
    }
}
